package com.adcc.ags.msgStore.dao;

import com.adcc.ags.msgStore.common.Constant;
import com.adcc.ags.msgStore.entity.db.DBMessage;
import com.adcc.ags.msgStore.entity.db.DBErrorCode;
import com.adcc.ags.msgStore.entity.file.FileErrorCode;
import com.adcc.ags.msgStore.pool.C3P0Pool;
import com.adcc.utility.log.Log;
import com.google.common.base.Optional;
import com.google.common.base.Strings;

import java.io.*;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

/**
 * MsgStoreDaoImpl
 */
public class MsgStoreDaoImpl implements MsgStoreDao {

    // 单例对象
    private static MsgStoreDao instance;

    private MsgStoreDaoImpl(){

    }

    /**
     * 单例模式
     * */
    public synchronized static MsgStoreDao getInstance() {
        if (instance == null) {
            instance = new MsgStoreDaoImpl();
        }
        return instance;
    }

    /**
     * 将报文信息保存到oracle数据库中
     * @param dbMessage
     * @return
     */
    @Override
    public DBErrorCode saveDBMessage(DBMessage dbMessage) {
        Optional<Connection> con = null;
        PreparedStatement pre = null;
        ResultSet result = null;
        DBErrorCode errorCode = DBErrorCode.NO_ERROR;
        String sql = "INSERT INTO " +Constant.TRAFFIC_TABLE_NAME+ " " +
                "(\"DATETIME\",\"MSGTYPE\",\"SENDER\",\"RECEIVER\",\"RAWMSG\",\"PART1\",\"PART2\",\"PART3\",\"PART4\",\"PART5\",\"ERROR_CODE\",\"MSGID\",\"ERROR_USER_ID\",\"ID\",\"TYPE\",\"LONGMSG\") " +
                "VALUES ( ? , ? , ? , ? ,? , ? ,? , ? ,? , ?, ?, ?, ?, ? ,?,empty_blob())";
        int rst = 0;
        try {
            Map<String,Object> map =getId();
            if(map.get(Constant.TRAFFIC_ID_KEY)==null || map.get(Constant.TRAFFIC_ID_KEY).toString().length() == 0){
                return (DBErrorCode)map.get(Constant.ERROR_CODE_KEY);
            }
            con = C3P0Pool.getConnection();
            if(con.isPresent()) {
                pre = con.get().prepareStatement(sql);
                pre.setTimestamp(1, new Timestamp(dbMessage.getDatetime().getTime()));
                pre.setString(2, dbMessage.getMsgType());
                pre.setString(3, dbMessage.getSender());
                pre.setString(4, dbMessage.getReceiver());
                pre.setString(5, dbMessage.getRawMsg());
                pre.setString(6, dbMessage.getPart1());
                pre.setString(7, dbMessage.getPart2());
                pre.setString(8, dbMessage.getPart3());
                pre.setString(9, dbMessage.getPart4());
                pre.setString(10, dbMessage.getPart5());
                pre.setString(11,dbMessage.getErrorCode());
                pre.setString(12,dbMessage.getMsgId());
                pre.setString(13,dbMessage.getErrorUserId());
                pre.setString(14, map.get("ID").toString());
                pre.setInt(15, dbMessage.getType());
                rst = pre.executeUpdate();
            }else{
                //连接异常时获取不到连接对象
                errorCode = DBErrorCode.CONNECT_ERROR;
            }
            //如果有大报文
            if(dbMessage.getLongMsg()!=null && dbMessage.getLongMsg().length > 0){
                String selectSql = "SELECT LONGMSG FROM " + Constant.TRAFFIC_TABLE_NAME + "  WHERE MSGID=?  FOR UPDATE";
                pre = con.get().prepareStatement(selectSql);
                pre.setString(1,dbMessage.getMsgId());
                ResultSet rset=pre.executeQuery();
                byte[] b = dbMessage.getLongMsg();
                if (rset.next()) {
                    oracle.sql.BLOB blob = (oracle.sql.BLOB) rset.getBlob("LONGMSG");
                    OutputStream out = blob.setBinaryStream(0);
                    try {
                        out.write(b);
                        out.flush();
                        out.close();
                    } catch (IOException e) {
                        Log.error(MsgStoreDaoImpl.class.getName(), "Failed to save LONGMSG to oracle database.", e);
                    }

                }
                con.get().commit();
            }
        } catch (SQLException e) {
            Log.error(MsgStoreDaoImpl.class.getName(),"Failed to set values in prepareStatement.", e);
            //此时发生的错误为插入异常，而不是连接异常
            errorCode = DBErrorCode.SEND_ERROR;
        } finally {
            C3P0Pool.close(con.get(), pre, result);
        }
        if (0 == rst) {
            Log.error(MsgStoreDaoImpl.class.getName(), "Execute insert sql fail. sql:" + sql);
        }
        return errorCode;
    }

    /**
     * 更新oracle数据库中的报文信息
     * @param dbMessage
     * @return
     */
    @Override
    public DBErrorCode updateDBMessage(DBMessage dbMessage) {
        Optional<Connection> con = null;
        PreparedStatement pre = null;
        ResultSet result = null;
        DBErrorCode errorCode = DBErrorCode.NO_ERROR;
        String sql = "UPDATE " + Constant.TRAFFIC_TABLE_NAME + " SET \"RECEIVER\"= ? ,\"ERROR_USER_ID\"= ?,\"ERROR_CODE\"= ? WHERE \"MSGID\" = ? ";
        int rst = 0;
        try {
            con = C3P0Pool.getConnection();
            if(con.isPresent()) {
                pre = con.get().prepareStatement(sql);
                pre.setString(1, dbMessage.getReceiver());
                pre.setString(2, dbMessage.getErrorUserId());
                pre.setString(3, dbMessage.getErrorCode());
                pre.setString(4, dbMessage.getMsgId());
                rst = pre.executeUpdate();
            }else{
                //连接异常时获取不到连接对象
                errorCode = DBErrorCode.CONNECT_ERROR;
            }
        } catch (SQLException e) {
            Log.error(MsgStoreDaoImpl.class.getName(),"Failed to set values in prepareStatement.", e);
            //此时发生的错误为插入异常，而不是连接异常
            errorCode = DBErrorCode.UPDATE_ERROR;
        } finally {
            C3P0Pool.close(con.get(), pre, result);
        }
        if (0 == rst) {
            Log.error(MsgStoreDaoImpl.class.getName(), "Execute update sql fail. sql:" + sql);
        }
        return errorCode;
    }

    private Map<String,Object> getId(){
        Optional<Connection> con = null;
        PreparedStatement pre = null;
        ResultSet result = null;
        DBErrorCode errorCode = DBErrorCode.NO_ERROR;
        String sql = "SELECT " + Constant.TRAFFIC_SEQUENCE_KEY + " AS \"ID\" FROM dual";
        Map<String,Object> map = new HashMap<String,Object>();
        try {
            con = C3P0Pool.getConnection();
            if(con.isPresent()) {
                pre = con.get().prepareStatement(sql);
                result = pre.executeQuery();
                while (result.next()) {
                    map.put(Constant.TRAFFIC_ID_KEY,result.getString(Constant.TRAFFIC_ID_KEY));
                }
            }else{
                //连接异常时获取不到连接对象
                errorCode = DBErrorCode.CONNECT_ERROR;
            }
        } catch (SQLException e) {
            Log.error(MsgStoreDaoImpl.class.getName(),"Failed to executeQuery .", e);
            //此时发生的错误为查询异常，而不是连接异常
            errorCode = DBErrorCode.SELECT_ERROR;
        } finally {
            C3P0Pool.close(con.get(), pre, result);
        }
        map.put(Constant.ERROR_CODE_KEY,errorCode);
        return map;
    }

    /**
     * 通过msgId检测该条报文是否已入库
     * @param msgId
     * @return
     */
    @Override
    public Map<String,Object> isExit(String msgId) {
        Optional<Connection> con = null;
        PreparedStatement pre = null;
        ResultSet result = null;
        DBErrorCode errorCode = DBErrorCode.NO_ERROR;
        String sql = "SELECT \"MSGID\" FROM" + Constant.TRAFFIC_TABLE_NAME + " WHERE \"MSGID\" = ?";
        Map<String,Object> map = new HashMap<String,Object>();
        try {
            con = C3P0Pool.getConnection();
            if(con.isPresent()) {
                pre = con.get().prepareStatement(sql);
                pre.setString(1, msgId.trim());
                result = pre.executeQuery();
                if (result.next()) {
                    map.put(Constant.IS_EXIT_KEY,true);
                }else {
                    map.put(Constant.IS_EXIT_KEY,false);
                }
            }else{
                //连接异常时获取不到连接对象
                errorCode = DBErrorCode.CONNECT_ERROR;
            }
        } catch (SQLException e) {
            Log.error(MsgStoreDaoImpl.class.getName(),"Failed to executeQuery .", e);
            //此时发生的错误为插入异常，而不是连接异常
            errorCode = DBErrorCode.SELECT_ERROR;
        } finally {
            C3P0Pool.close(con.get(), pre, result);
        }
        map.put(Constant.ERROR_CODE_KEY,errorCode);
        return map;
    }


    /**
     * 通过msgId检测该条报文是否已入库
     * @param msgId
     * @return
     */
    @Override
    public DBMessage findMsgById(String msgId) {
        Optional<Connection> con = null;
        PreparedStatement pre = null;
        ResultSet result = null;
        DBErrorCode errorCode = DBErrorCode.NO_ERROR;
        String sql = "SELECT \"MSGID\" , \"RECEIVER\" ,\"ERROR_USER_ID\", \"ERROR_CODE\" FROM" + Constant.TRAFFIC_TABLE_NAME + " WHERE \"MSGID\" = ?";
        DBMessage dbMessage = null;
        try {
            con = C3P0Pool.getConnection();
            if(con.isPresent()) {
                pre = con.get().prepareStatement(sql);
                pre.setString(1, msgId.trim());
                result = pre.executeQuery();
                while(result.next()) {
                    dbMessage = new DBMessage();
                    dbMessage.setMsgId(result.getString("MSGID"));
                    dbMessage.setReceiver(result.getString("RECEIVER"));
                    dbMessage.setErrorUserId(result.getString("ERROR_USER_ID"));
                    dbMessage.setErrorCode(result.getString("ERROR_CODE"));
                }
            }else{
                //连接异常时获取不到连接对象
            }
        } catch (SQLException e) {
            Log.error(MsgStoreDaoImpl.class.getName(),"Failed to executeQuery .", e);
            //此时发生的错误为插入异常，而不是连接异常
        } finally {
            C3P0Pool.close(con.get(), pre, result);
        }
        return dbMessage;
    }

    /**
     * 将报文保存到文件中
     * @param message
     * @param filePath
     * @param name
     * @return
     */
    @Override
    public FileErrorCode saveFile(byte[] message, String filePath,String name) {
        BufferedOutputStream bos = null;
        FileOutputStream fos = null;
        File file = null;
        FileErrorCode fileError = FileErrorCode.NO_ERROR;
        if(Strings.isNullOrEmpty(filePath)){
            fileError = FileErrorCode.PATH_ERROR;
            Log.error(getClass().getName(), "file path is null!");
            return fileError;
        }
        try {
            File dir = new File(filePath);
            if(!dir.exists()&&dir.isDirectory()){//判断文件目录是否存在
                dir.mkdirs();
            }
            file = new File(filePath+File.separator + name);
            fos = new FileOutputStream(file);
            bos = new BufferedOutputStream(fos);
            bos.write(message);
        } catch (Exception e) {
            fileError = FileErrorCode.SAVE_ERROR;
            Log.error(getClass().getName(), "save message to file error!", e);
        } finally {
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e1) {
                    fileError = FileErrorCode.CLOSE_ERROR;
                    Log.error(getClass().getName(), "Close BufferedOutputStream object error!", e1);
                }
            }
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    fileError = FileErrorCode.CLOSE_ERROR;
                    Log.error(getClass().getName(),"Close FileOutputStream object error!",e1);
                }
            }
        }
        return fileError;
    }
}
